﻿
CREATE OR REPLACE FUNCTION creditos.getFuncCalMora(_id_cuota varchar(7))
  RETURNS text AS
$BODY$
declare
	data record;
	mora double precision ;
begin
select generaIntereses(CC.IMP_CUOTA,convertirTemToTead(5), CAST( now() AS DATE)-CAST(FEC_VENCIMIENTO AS DATE) ) into mora from creditos.v_listar_imp_cal_mora cc
where cc.SALDO>0 and FEC_VENCIMIENTO<now() and cc.nro_cuota<>0 
	and CC.ID_CUOTA=_ID_CUOTA ;
if mora is not Null then
	return mora	;	
else
	return 0;
end if;
--return mora;	
end;$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

select  creditos.getFuncCalMora('4') from creditos.cuota

select * from creditos.v_lista_cutotas_ctas_xpagar   where idcliente= '15'  order by idcuota asc

select creditos.getFuncCalMora('131');

create or replace function generaIntereses(P  numeric,i NUMERIC, n INTEGER)
returns numeric as $$
declare
	IT NUMERIC;
	tasaAntesElevar NUMERIC;
	tasaDespuesElevar NUMERIC;
begin
	 tasaAntesElevar = (1+(i/100));
        tasaDespuesElevar = power(tasaAntesElevar,n);
        IT=P*(tasaDespuesElevar-1);
        return IT;
end;$$
language 'plpgsql';



create or replace function convertirTeaToTeam( tasa numeric)
returns numeric as $$
declare
	 temSalida numeric;
	 tasaAntesElevar numeric;
	 tasaDespuesElevar numeric;
	 indice numeric;
	 NUM numeric;
	 DEN numeric;
begin
	NUM=1;
        DEN=12;        
        tasaAntesElevar = (1+(tasa/100));
        indice=NUM/DEN;
        tasaDespuesElevar = power(tasaAntesElevar,indice);
        temSalida=(tasaDespuesElevar-1)*100; --// Volviendo la tasa a %
        return temSalida; 
	
end;$$
language 'plpgsql';




create or replace function convertirTemToTead( tasa numeric)
returns numeric as $$
declare
	 temSalida numeric;
	 tasaAntesElevar numeric;
	 tasaDespuesElevar numeric;
	 indice numeric;
	 NUM numeric;
	 DEN numeric;
begin
	NUM=1;
        DEN=30;        
        tasaAntesElevar = (1+(tasa/100));
        indice=NUM/DEN;
        tasaDespuesElevar = power(tasaAntesElevar,indice);
        temSalida=(tasaDespuesElevar-1)*100; --// Volviendo la tasa a %
        return temSalida; 
	
end;$$
language 'plpgsql';



create or replace function convertirTemToTea( tasa numeric)
returns numeric as $$
declare
	 teaSalida numeric;
	 tasaAntesElevar numeric;
	 tasaDespuesElevar numeric;
	 indice numeric;
	 NUM numeric;
	 DEN numeric;
begin

        tasaAntesElevar = (1+(tasa/100));
        DEN=12;
        indice=DEN;
        tasaDespuesElevar = power(tasaAntesElevar,indice);
        teaSalida=(tasaDespuesElevar-1)*100; --  // Volviendo la tasa a %
        return teaSalida;
	
end;$$
language 'plpgsql';



create or replace view creditos.v_listar_imp_cal_mora
as
select cc.idcuenta_xcobrar as id_credito, 
        cc.idcuota as id_cuota, 
	cc.total_cuota as IMP_CUOTA,
	cc.fecha_vencimiento as FEC_VENCIMIENTO,
        (CC.total_cuota+CC.importe_mora-COALESCE((SELECT sum(M.monto_total) FROM operaciones.movimiento M 
	WHERE CC.IDCUOTA=M.IDCUOTA and  (m.anulado='N' or m.anulado='0') ) ,0)) SALDO,
	nrocuota::integer as nro_cuota
FROM CREDITOS.CUOTA CC
order by cc.nrocuota asc


select *from operaciones.movimiento
select *from creditos.cuota
